﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

/*
 * Author:Conis
 * Date:2009-03-03
 * Description:数据访问的基础类，此类包含对数据访问的方法、对SQL的组装等
 */


namespace Conis.Squirrel.SqlBuilder
{
    /// <summary>
    /// 数据访问的基础类，此类包含对数据访问的方法、对SQL的组装等
    /// </summary>
    public abstract class BaseDataAccess
    {
        /// <summary>
        /// 表名
        /// </summary>
        protected string TableName { get; set; }
        /// <summary>
        /// 主键名
        /// </summary>
        protected string PrimaryKey { get; set; }
        /// <summary>
        /// SQL工厂的接口
        /// </summary>
        protected BaseSqlFactory SqlFactory { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlFactory">SQL的工具类</param>
        /// <param name="tableName">表的名称</param>
        /// <param name="primaryKey">主健的名称</param>
        public BaseDataAccess(BaseSqlFactory sqlFactory, string tableName, string primaryKey)
        {
            this.SqlFactory = sqlFactory;
            this.TableName = tableName;
            this.PrimaryKey = primaryKey;
        }

        #region 抽象方法
        protected abstract IDataParameter GetParameter(string key, object value);
        protected abstract DataTable GetSinglePage(Pagination pagination, string condition, string fields, string orderBy, params IDataParameter[] parameters);
        #endregion

        #region 插入及更新
        /// <summary>
        /// 获取插入的SQL语句
        /// </summary>
        /// <param name="nameValue">字段名与值的Hashtable</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        /// <param name="ignorePrimaryKey">是否忽略主键</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回插入数据的SQL语句</returns>
        protected virtual string GetInsertSql(Hashtable nameValue, bool ignoreNull, bool ignorePrimaryKey, out IDataParameter[] parameters)
        {
            StringBuilder sbFields = new StringBuilder();           //字段名收集器
            StringBuilder sbValues = new StringBuilder();           //值收集器
            List<IDataParameter> list = new List<IDataParameter>();
            int index = 0;
            string guid;
            foreach (DictionaryEntry item in nameValue)
            {
                //如果值为Null，且忽略空值，则跳过；如果忽略主键，且当前键为主键，则跳过
                if ((item.Value == null && ignoreNull) ||
                    (ignorePrimaryKey && item.Key.ToString() == this.PrimaryKey))
                {
                    continue;
                }
                //不是第一个，需要加上逗号
                if (index > 0)
                {
                    sbFields.Append(",");
                    sbValues.Append(",");
                }

                //值为Null但不忽略
                if (item.Value == null)
                {
                    sbFields.Append(item.Key);
                    sbValues.Append("NULL");
                }
                else
                {
                    guid = "@";
                    guid += Guid.NewGuid().ToString("N");
                    sbFields.Append(item.Key);
                    sbValues.Append(guid);
                    list.Add(this.GetParameter(guid, item.Value));
                }
                index++;
            }
            string sql = "INSERT INTO [{0}]({1}) VALUES ({2})";
            parameters = list.ToArray();
            return string.Format(sql, this.TableName, sbFields.ToString(), sbValues.ToString());
        }

        /// <summary>
        /// 插入一条记录，并返回主键
        /// </summary>
        /// <param name="nameValue">字段名与值的Hashtable</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        /// <param name="ignorePrimaryKey">是否忽略主键</param>
        /// <returns>返回刚插入的数据主键</returns>
        public virtual object Insert(Hashtable nameValue, bool ignoreNull, bool ignorePrimaryKey)
        {
            IDataParameter[] parameters;
            string sql = this.GetInsertSql(nameValue, ignoreNull, ignorePrimaryKey, out parameters);
            this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text, parameters);

            //获取刚刚插入的主键，最大的键值
            sql = string.Format("SELECT MAX({0}) FROM {1}", this.PrimaryKey, this.TableName);
            object result = this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text);
            return result;
        }

        /// <summary>
        /// 插入入一个数据实体，并返回主键
        /// </summary>
        /// <param name="concrete">要插入的数据实体</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        /// <param name="ignorePrimaryKey">是否忽略主键</param>
        /// <returns></returns>
        public virtual object Insert(BaseConcrete concrete, bool ignoreNull, bool ignorePrimaryKey)
        {
            return this.Insert(concrete.Container, ignoreNull, ignorePrimaryKey);
        }

        /// <summary>
        /// 根据数据实体，更新一条数据
        /// </summary>
        /// <param name="concrete">要更新的数据实体</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        public virtual void Update(BaseConcrete concrete, bool ignoreNull)
        {
            object primaryKey = concrete.Container[this.PrimaryKey];
            if (primaryKey == null)
            {
                throw new Exception("错误： 主键不能为空。");
            }
            BaseCondition condition = this.SqlFactory.Condition;
            condition.AddCompare(Connector.And, this.PrimaryKey, CompareOperator.Equal, primaryKey.ToString());
            System.Collections.Hashtable ht = concrete.Container.Clone() as System.Collections.Hashtable;
            ht.Remove(this.PrimaryKey);             //删除主键，更新不需要主键
            this.Update(condition, ht, ignoreNull);
        }

        /// <summary>
        /// 根据条件更新记录
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <param name="nameValue">字段对应的值</param>
        /// <param name="ignoreNull">是否忽略空值</param>
        public virtual void Update(BaseCondition condition, Hashtable nameValue, bool ignoreNull)
        {
            if (nameValue.Count == 0)
            {
                return;
            }
            StringBuilder sb = new StringBuilder("UPDATE ").Append(this.TableName).Append(" SET ");
            List<IDataParameter> paramArray = new List<IDataParameter>();
            int index = 0;
            string guid;
            foreach (DictionaryEntry item in nameValue)
            {
                //如果值为Null，且忽略空值，则跳过当次更新
                if (item.Value == null && ignoreNull)
                {
                    continue;
                }
                //不是第一个，需要加上逗号
                if (index != 0)
                {
                    sb.Append(",");
                }

                //值为Null但不忽略
                if (item.Value == null)
                {
                    sb.Append(item.Key).Append(" = NULL");
                }
                else
                {
                    guid = "@" + Guid.NewGuid().ToString("N");
                    sb.Append(item.Key.ToString()).Append(" = ").Append(guid);
                    paramArray.Add(this.GetParameter(guid, item.Value));
                }
                index++;
            }

            string conditionStr = string.Empty;
            if (condition != null)
            {
                conditionStr = condition.ToString(true, true);
                //paramArray.AddRange(.ToArray<SQLiteParameter>());
                foreach (IDataParameter item in condition.Parameters())
                {
                    paramArray.Add(item);
                }
            }
            string sql = sb.ToString() + conditionStr;
            this.SqlFactory.ExecuteSql.ExecuteNonQuery(sql, CommandType.Text, paramArray.ToArray());
        }
        #endregion

        #region 查询
        /// <summary>
        /// 根据条件，返回查询的结果并排序
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="fields">查询的字段</param>
        /// <returns>返回查询结果</returns>
        public virtual DataTable Get(BaseCondition condition, BaseOrderBy orderBy, params string[] fields)
        {
            IDataParameter[] parameters = null;

            string fieldsStr = "*";
            if (fields.Length > 0)
            {
                fieldsStr = string.Join(",", fields);
            }
            //排序
            string orderStr = string.Empty;
            if (orderBy != null)
            {
                orderStr = orderBy.ToString();
            }
            //条件
            string conditionStr = string.Empty;
            if (condition != null)
            {
                conditionStr = condition.ToString(true, true);
                parameters = condition.Parameters();
            }



            string sql = string.Format("SELECT {0} FROM {1}{2}{3}", fieldsStr, this.TableName, conditionStr, orderStr);
            return this.SqlFactory.ExecuteSql.GetDataTable(sql, CommandType.Text, parameters);

        }

        /// <summary>
        /// 根据条件，返回查询的结果并排序分页
        /// </summary>
        /// <param name="pagination">分页信息</param>
        /// <param name="condition">查询条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="fields">查询的字段</param>
        /// <returns>返回查询结果</returns>
        public virtual DataTable Get(ref Pagination pagination, BaseCondition condition, BaseOrderBy orderBy, params string[] fields)
        {
            if (pagination.PageIndex == 0)
            {
                throw new Exception("错误：页索引PageIndex必需大于0");
            }

            //获取排序，条件，字段列表
            string fieldsStr = fields.Length == 0 ? "*" : string.Join(",", fields);
            string orderStr = orderBy == null ? string.Empty : orderBy.ToString(true);
            string conditionStr = string.Empty;
            IDataParameter[] parameters = null;
            if (condition == null)
            {
                conditionStr = condition.ToString(true, true);
                parameters = condition.Parameters();
            }

            string sql;
            DataTable dt = this.GetSinglePage(pagination, conditionStr, fieldsStr, orderStr, parameters);
            pagination.CurrentCount = dt.Rows.Count;

            //获取总记录数
            sql = string.Format("SELECT COUNT(*) FROM {0}{1}", this.TableName, conditionStr);
            object result = this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text, parameters);
            pagination.RecordCount = Convert.ToInt32(result);
            pagination = PaginationHelper.EvalPagination(pagination);
            return dt;
        }

        /// <summary>
        /// 根据主键获取一个数据实体
        /// </summary>
        /// <param name="primaryKey">主健</param>
        /// <param name="concrete">数据实体</param>
        /// <returns>是否成功获取</returns>
        public virtual bool Get(string primaryKey, ref BaseConcrete concrete)
        {
            BaseCondition condition = this.SqlFactory.Condition;
            condition.AddCompare(Connector.And, this.PrimaryKey, CompareOperator.Equal, primaryKey);
            DataTable dt = this.Get(condition, null);
            if (dt.Rows.Count != 1)
            {
                return false;
            }
            else
            {
                concrete = concrete.FromDataRow(dt.Rows[0]);
                return true;
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 根据条件删除记录
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <returns>返回成功删除的记录数</returns>
        public virtual int Delete(BaseCondition condition)
        {
            IDataParameter[] parameters = null;
            //条件
            string conditionStr = string.Empty;
            if (condition != null)
            {
                conditionStr = condition.ToString(true, true);
                parameters = condition.Parameters();
            }
            string sql = string.Format("DELETE FROM {0}{1}", this.TableName, conditionStr);
            return this.SqlFactory.ExecuteSql.ExecuteNonQuery(sql, CommandType.Text, parameters);
        }


        /// <summary>
        /// 根据主键删除数据
        /// </summary>
        /// <param name="primaryKey">主健</param>
        /// <returns>返回成功删除的记录数</returns>
        public virtual int DeleteByPrimaryKey(string primaryKey)
        {
            BaseCondition condition = this.SqlFactory.Condition;
            condition.AddCompare(Connector.And, this.PrimaryKey, CompareOperator.Equal, primaryKey);
            return this.Delete(condition);
        }
        #endregion


        /// <summary>
        /// 根据条件，查询符合条件的总记录数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns>返回符合条件的总记录数</returns>
        public virtual int Count(BaseCondition condition)
        {
            IDataParameter[] parameters = null;
            //条件
            string conditionStr = string.Empty;
            if (condition != null)
            {
                conditionStr = condition.ToString(true, true);
                parameters = condition.Parameters();
            }
            string sql = string.Format("SELECT COUNT(*) FROM {0}{1}", this.TableName, conditionStr);
            object result = this.SqlFactory.ExecuteSql.ExecuteScalar(sql, CommandType.Text, parameters);
            return Convert.ToInt32(result);
        }
    }
}
